﻿using Microsoft.AspNetCore.Http;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.ComponentModel;
using System.Data;
using System.Globalization;
using System.Text;

namespace IotUtil
{
    public class ExcelHelper
    {
        public static List<T> InputExcel<T>(IFormFile file, int rowindex = 1) where T : class, new()
        {
            List<T> list = new List<T> { };

            MemoryStream ms = new MemoryStream();
            file.CopyTo(ms);
            ms.Seek(0, SeekOrigin.Begin);

            IWorkbook workbook = new HSSFWorkbook(ms);
            ISheet sheet = workbook.GetSheetAt(0);
            IRow cellNum = sheet.GetRow(0);
            var propertys = typeof(T).GetProperties();
            string value = null;
            int num = cellNum.LastCellNum;

            for (int i = 1; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                var obj = new T();
                for (int j = 0; j < num; j++)
                {
                    value = string.Empty;
                    if (row.GetCell(j) != null)
                    {
                        value = row.GetCell(j).ToString();
                    }
                    string str = (propertys[j].PropertyType).FullName;
                    if (str == "System.String")
                    {
                        propertys[j].SetValue(obj, value, null);
                    }
                    else if (str == "System.DateTime")
                    {
                        DateTime pdt = Convert.ToDateTime(value, CultureInfo.InvariantCulture);
                        propertys[j].SetValue(obj, pdt, null);
                    }
                    else if (str == "System.Boolean")
                    {
                        bool pb = Convert.ToBoolean(value);
                        propertys[j].SetValue(obj, pb, null);
                    }
                    else if (str == "System.Int16")
                    {
                        short pi16 = Convert.ToInt16(value);
                        propertys[j].SetValue(obj, pi16, null);
                    }
                    else if (str == "System.Int32")
                    {
                        int pi32 = Convert.ToInt32(value);
                        propertys[j].SetValue(obj, pi32, null);
                    }
                    else if (str == "System.Int64")
                    {
                        long pi64 = Convert.ToInt64(value);
                        propertys[j].SetValue(obj, pi64, null);
                    }
                    else if (str == "System.Byte")
                    {
                        byte pb = Convert.ToByte(value);
                        propertys[j].SetValue(obj, pb, null);
                    }
                    else
                    {
                        propertys[j].SetValue(obj, value, null);
                    }
                }

                list.Add(obj);
            }

            return list;
        }


        public static List<T> InputExcelXLSX<T>(IFormFile file, int rowindex = 1) where T : class, new()
        {
            List<T> list = new List<T> { };

            MemoryStream ms = new MemoryStream();
            file.CopyTo(ms);
            ms.Seek(0, SeekOrigin.Begin);

            IWorkbook workbook = new XSSFWorkbook(ms);
            ISheet sheet = workbook.GetSheetAt(0);
            IRow cellNum = sheet.GetRow(rowindex);
            var propertys = typeof(T).GetProperties();
            string value = null;
            //int num = cellNum.LastCellNum;
            int num = 13;//目前到excel的分组3列 后续扩充在改,因为实际配置过程中,excel表格会加一些跟平台无关自定义的列,导致上传出错.

            for (int i = rowindex; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                var obj = new T();
                for (int j = 0; j < num; j++)
                {
                    if (row.GetCell(j) == null)
                        continue;
                    value = row.GetCell(j).ToString();
                    string str = (propertys[j].PropertyType).FullName;
                    if (str == "System.String")
                    {
                        propertys[j].SetValue(obj, value, null);
                    }
                    else if (str == "System.DateTime")
                    {
                        DateTime pdt = Convert.ToDateTime(value, CultureInfo.InvariantCulture);
                        propertys[j].SetValue(obj, pdt, null);
                    }
                    else if (str == "System.Boolean")
                    {
                        bool pb = Convert.ToBoolean(value);
                        propertys[j].SetValue(obj, pb, null);
                    }
                    else if (str == "System.Int16")
                    {
                        short pi16 = Convert.ToInt16(value);
                        propertys[j].SetValue(obj, pi16, null);
                    }
                    else if (str == "System.Int32")
                    {
                        int pi32 = Convert.ToInt32(value);
                        propertys[j].SetValue(obj, pi32, null);
                    }
                    else if (str == "System.Int64")
                    {
                        long pi64 = Convert.ToInt64(value);
                        propertys[j].SetValue(obj, pi64, null);
                    }
                    else if (str == "System.Byte")
                    {
                        byte pb = Convert.ToByte(value);
                        propertys[j].SetValue(obj, pb, null);
                    }
                    else
                    {
                        propertys[j].SetValue(obj, value, null);
                    }
                }

                list.Add(obj);
            }

            return list;
        }

        public static async Task<DataTable> ExcelToDataTable(IFormFile file)
        {
            if (file == null || file.Length == 0)
            {
                return null;
            }
            IWorkbook workbook;
            DataTable dt = new DataTable();
            string fileExt = file.FileName.Substring(file.FileName.LastIndexOf(".")).ToLower();

            using (var fs = file.OpenReadStream())
            {
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
                if (workbook == null)
                {
                    return null;
                }
                ISheet sheet = workbook.GetSheetAt(0);
                //表头  
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                List<int> columns = new List<int>();
                for (int i = 0; i < header.LastCellNum; i++)
                {
                    object obj = GetValueType(header.GetCell(i));
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据  
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow dr = dt.NewRow();
                    bool hasValue = false;
                    foreach (int j in columns)
                    {
                        dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }
        public static async Task<string> ExcelToJsonStr(IFormFile file)
        {
            if (file == null || file.Length == 0)
            {
                return null;
            }
            IWorkbook workbook;
            DataTable dt = new DataTable();
            string fileExt = file.FileName.Substring(file.FileName.LastIndexOf(".")).ToLower();

            using (var fs = file.OpenReadStream())
            {
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
                if (workbook == null)
                {
                    return null;
                }
                ISheet sheet = workbook.GetSheetAt(0);
                //表头  
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                List<int> columns = new List<int>();
                for (int i = 0; i < header.LastCellNum; i++)
                {
                    object obj = GetValueType(header.GetCell(i));
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据  
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow dr = dt.NewRow();
                    bool hasValue = false;
                    foreach (int j in columns)
                    {
                        dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }

            }
            var rowCount = dt.Rows.Count;
            var colCount = dt.Columns.Count;
            StringBuilder sb = new StringBuilder();
            sb.Append("[");
            for (var rowIndex = 0; rowIndex < rowCount; rowIndex++)
            {
                var row = dt.Rows[rowIndex];
                sb.Append("{");
                for (var colIndex = 0; colIndex < colCount; colIndex++)
                {
                    var cellValueObj = row[colIndex];
                    var cellValue = cellValueObj?.ToString();
                    var column = dt.Columns[colIndex];
                    sb.Append("\"").Append(column.ColumnName).Append("\":\"").Append(cellValue).Append("\"");
                    if (colIndex < colCount - 1)
                    {
                        sb.Append(",");
                    }
                }
                sb.Append("}");
                if (rowIndex < rowCount - 1)
                {
                    sb.Append(",");
                }
            }
            sb.Append("]");
            return sb.ToString();
        }
        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:  
                    return null;
                case CellType.Boolean: //BOOLEAN:  
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:  
                    return cell.NumericCellValue;
                case CellType.String: //STRING:  
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:  
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:  
                default:
                    return "=" + cell.CellFormula;
            }
        }



        /// <summary>
        /// 检查Excel数据是否有误 并且生成List T
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="fields">插入的字段数组 </param>
        /// <param name="fieldIndex">确认当前行数据是否存在的索引号</param>
        /// <param name="cfields">要进行变形的字段数组 </param>
        /// <param name="file"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public static List<T> CheckProperties<T>(string[] fields, int fieldIndex, string[] cfields, IFormFile file, out string msg) where T : class, new()
        {
            List<T> list = new List<T>();
            msg = "";

            MemoryStream ms = new MemoryStream();
            file.CopyTo(ms);
            ms.Seek(0, SeekOrigin.Begin);

            IWorkbook workbook = new HSSFWorkbook(ms);
            ISheet sheet = workbook.GetSheetAt(0);
            IRow cellNum = sheet.GetRow(0);

            int num = cellNum.LastCellNum;

            var propertys = typeof(T).GetProperties();

            for (int i = 1; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)//如果当前行不存在则返回
                {
                    break;
                }
                if (string.IsNullOrEmpty(row.GetCell(fieldIndex).ToString())) //如果设定的列为空则认为此行数据为空
                {
                    continue;
                }

                var obj = new T();
                bool b = false;
                for (int j = 0; j < fields.Length; j++)  //每列的数据
                {
                    /* if(j == 8)
                     {
                         int mmm = 0;
                     }*/
                    string value = "";
                    if (row.GetCell(j) != null)
                    {
                        value = row.GetCell(j).ToString();
                    }
                    for (int k = 0; k < cfields.Length; k++) //如果是确定的要进行还原改变的字段 则进行变更
                    {
                        if (fields[j].ToLower() == cfields[k].ToLower())
                        {
                            string[] vls = value.Split("-");
                            value = vls[vls.Length - 1];
                        }
                    }
                    if (string.IsNullOrEmpty(value))
                    {
                        continue;
                    }
                    foreach (var item in propertys)
                    {
                        if (item.Name.ToLower() == fields[j].ToLower())
                        {
                            string str = (item.PropertyType).FullName;
                            try
                            {
                                if (str == "System.String")
                                {
                                    item.SetValue(obj, value, null);
                                }
                                else if (str.Contains("System.DateTime"))  //DateTime? 这种形式 是 Nullable`
                                {
                                    DateTime pdt = Convert.ToDateTime(value, CultureInfo.InvariantCulture);
                                    item.SetValue(obj, pdt, null);
                                }
                                else if (str == "System.Boolean")
                                {
                                    bool pb = Convert.ToBoolean(value);
                                    item.SetValue(obj, pb, null);
                                }
                                else if (str.Contains("System.Int16"))
                                {
                                    short pi16 = Convert.ToInt16(value);
                                    item.SetValue(obj, pi16, null);
                                }
                                else if (str.Contains("System.Int32"))
                                {
                                    int pi32 = Convert.ToInt32(value);
                                    item.SetValue(obj, pi32, null);
                                }
                                else if (str.Contains("System.Int64"))
                                {
                                    long pi64 = Convert.ToInt64(value);
                                    item.SetValue(obj, pi64, null);
                                }
                                else if (str == "System.Byte")
                                {
                                    byte pb = Convert.ToByte(value);
                                    item.SetValue(obj, pb, null);
                                }
                                else
                                {
                                    item.SetValue(obj, value, null);
                                }
                            }
                            catch (Exception ex)
                            {
                                b = true;
                            }
                        }
                        if (b)
                        {
                            break; //跳出foreach 循环
                        }
                    }
                    if (b)
                    {
                        break; //跳出 for j 循环
                    }
                }
                if (b)
                {
                    msg += $"第{i + 1}行;";
                    continue; //如果有错误 则跳过 并且记录信息

                }
                list.Add(obj);
            }
            if (msg != "")
            {
                msg += "数据类型有误,请修正后插入!";
            }
            ms.Close();
            ms.Dispose();
            return list;
        }

        public static List<T> InputWrittenExcelXLSX<T>(IFormFile file, int rowindex = 1) where T : class, new()
        {
            List<T> list = new List<T> { };

            MemoryStream ms = new MemoryStream();
            file.CopyTo(ms);
            ms.Seek(0, SeekOrigin.Begin);

            IWorkbook workbook = new XSSFWorkbook(ms);
            ISheet sheet = workbook.GetSheetAt(0);
            IRow cellNum = sheet.GetRow(rowindex);
            var propertys = typeof(T).GetProperties();
            string value = null;
            int num = cellNum.LastCellNum;
            for (int i = rowindex; i <= sheet.LastRowNum; i++)
            {
                List<double> valueList = new List<double>();
                IRow row = sheet.GetRow(i);
                var obj = new T();
                for (int j = 0; j < num; j++)
                {
                    string str = string.Empty;
                    if (row.GetCell(j) == null)
                    {
                        str = (propertys[3].PropertyType).FullName;
                        double d = 0;
                        valueList.Add(0);
                        continue;
                    }

                    value = row.GetCell(j).ToString();

                    if (j > 3)
                    {
                        str = (propertys[3].PropertyType).FullName;
                    }
                    else
                    {
                        str = (propertys[j].PropertyType).FullName;
                    }
                    if (str == "System.String")
                    {
                        propertys[j].SetValue(obj, value, null);
                    }
                    else if (str == "System.DateTime")
                    {
                        DateTime pdt = Convert.ToDateTime(value, CultureInfo.InvariantCulture);
                        propertys[j].SetValue(obj, pdt, null);
                    }
                    else if (str == "System.Boolean")
                    {
                        bool pb = Convert.ToBoolean(value);
                        propertys[j].SetValue(obj, pb, null);
                    }
                    else if (str == "System.Int16")
                    {
                        short pi16 = Convert.ToInt16(value);
                        propertys[j].SetValue(obj, pi16, null);
                    }
                    else if (str == "System.Int32")
                    {
                        int pi32 = Convert.ToInt32(value);
                        propertys[j].SetValue(obj, pi32, null);
                    }
                    else if (str == "System.Int64")
                    {
                        long pi64 = Convert.ToInt64(value);
                        propertys[j].SetValue(obj, pi64, null);
                    }
                    else if (str == "System.Byte")
                    {
                        byte pb = Convert.ToByte(value);
                        propertys[j].SetValue(obj, pb, null);
                    }
                    else if (str.Contains("System.Double"))
                    {
                        double d = 0;
                        if (!string.IsNullOrEmpty(value))
                        {
                            d = Convert.ToDouble(value);
                        }
                        valueList.Add(d);
                        // propertys[j].SetValue(obj, pi64, null);
                    }
                    else
                    {
                        propertys[j].SetValue(obj, value, null);
                    }
                }
                propertys[3].SetValue(obj, valueList, null);

                list.Add(obj);
            }

            return list;
        }


    }
}